#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sys,json
sys.path.append("./cow")
from database import con 

# 根据策略id获取股票列表
# strategyId 策略id
# type 类型 0股票 1 etf
def scheme_codes(strategyId:int,type:int):

    sql =  ("""
    SELECT code from scheme_codes
    WHERE scheme_id = %s and type= %s
    """ % (strategyId,type))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchall()
    codes = map(lambda x: x["code"],result)
    return codes

# 获取ETF交易日
def schemeETF_date(strategyId:int):
    codes = scheme_codes(strategyId,1)
    sql = ("""
    SELECT date FROM etfdaily
    WHERE code in (%s) AND open>0
    GROUP BY date
    """ % ",".join(map(lambda x: ("'%s'" %x),codes)))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchall()
    return result

# 获取股票交易日
def schemeStore_date(strategyId:int):
    codes = scheme_codes(strategyId,0)
    sql = ("""
    SELECT date FROM stockdaily
    WHERE code in (%s) AND open>0
    GROUP BY date
    """ % ",".join(codes))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchall()
    return result

# 获取综合交易日
def scheme_dates(strategyId:int):
    etfdates = schemeETF_date(strategyId=strategyId)
    storedates = schemeStore_date(strategyId=strategyId)
    etfd = map(lambda x: x["date"], etfdates)
    stored = map(lambda x: x["date"],storedates)
    dates = set(list(etfd)+list(stored))  
    return dates
# 获取etf的OHLC
def etfOHLC(code,date):
    sql = ("""
    SELECT * from etfdaily
    where date = '%s' and code='%s'
    """ % (date,code))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchone()
    return result

# 获取股票的OHLC
def storeOHLC(code,date):
    sql = ("""
    SELECT * from stockdaily
    where date = '%s' and code='%s'
    """ % (date,code))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchone()
    return result

# 获取策略参数
def scheme_param(strategyId:int):
    sql = ("""
    SELECT key,value from scheme_param
    WHERE scheme_id = %s
    """ % (strategyId))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchall()
    dics = {}
    for item in result:
        dics[item["key"]] = item["value"]
    return dics

# 添加回测记录
def scheme_contract(strategyId:int,date,order):
    code = order["code"]
    price = order["price"]
    count = order["count"]
    type =  order["type"]
    dir  = order["dir"]

    sql = ""
    if dir == 0:
        sql = ("""
        INSERT INTO "back_trade" ("scheme_id", "date", "type", "code", "price", "dir", "count")
        VALUES ('%s', '%s', '%s', '%s','%s', '%s', '%s');
        """ % (strategyId,date,type,code,price,dir,count))
        cur = con.cursor()
        cur.execute(sql)
        con.commit()
    elif dir == 1:
        sid = order["sid"]
        sql = ("""
        INSERT INTO "back_trade" ("scheme_id", "date", "type", "code", "price", "dir", "count","sid")
        VALUES ('%s', '%s', '%s', '%s','%s', '%s', '%s', '%s');
        """ % (strategyId,date,type,code,price,dir,count,sid))
        cur = con.cursor()
        cur.execute(sql)
        result = cur.fetchall()
        con.commit()
        scheme_updatebuycontractsid(id=sid,sid=cur.lastrowid)
    
# 卖出配对   
def scheme_updatebuycontractsid(id,sid):
    sql=("""
    UPDATE back_trade
    SET sid=%s
    WHERE id=%s
    """ % (sid,id))
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
# 清理对应策略的回测数据
def scheme_clear_backtrade(strategyId:int):
    sqls = [
        (" DELETE FROM back_trade where scheme_id=%s " % strategyId),
        # (" DELETE FROM back_trade where back_trade_surplus=%s " % strategyId)
    ]
    cur = con.cursor()
    for sql in sqls:
        cur.execute(sql)
    con.commit()
# 获取没有卖出的策略
def scheme_trade_nosellorder(strategyId:int,codes,type,date):
    codestr = ""
    if len(codes) > 0:
        wstr = ",".join(map(lambda x: ("'%s'" %x),codes))
        codestr = (" and code not in (%s) " % wstr)
  
    
    sql = ("""
    SELECT * FROM 
    (SELECT t0.*,t1.date sdate FROM  back_trade t0
    LEFT JOIN
    (
    SELECT * FROM  back_trade
    WHERE  dir=1
    ) t1 ON t1.sid = t0.id
    WHERE t0.dir = 0)

    WHERE scheme_id=%s and (sdate is NULL or sdate>'%s')
    and type=%s and date<=%s  %s
    """ % (strategyId,date,type,date,codestr))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchall()
    return result
# 获取没有卖出code对应最低成本
def scheme_trade_minprice(strategyId:int,code):
    
    sql = ("""
    SELECT min(price) as price FROM back_trade
    WHERE  scheme_id=%s and code='%s' and sid is NULL  
    """ % (strategyId,code))
    cur = con.cursor()
    cur.execute(sql)
    result = cur.fetchone()
   
    return result["price"]

def createTable():
    cur = con.cursor()
    
    # 策略模版表
    sql = """
    CREATE TABLE IF NOT EXISTS "scheme_template" ( -- 策略模版表
        "id"    INTEGER NOT NULL UNIQUE,
        "name"    TEXT NOT NULL, -- 方案名
        "des"    TEXT NOT NULL, -- 方案描述
        PRIMARY KEY("id" AUTOINCREMENT)
    );
    """
    cur.execute(sql)

    # 策略参数表    
    sql = """
        CREATE TABLE IF NOT EXISTS "scheme_template_param" ( -- 策略参数表
            "id"    INTEGER NOT NULL UNIQUE,
            "template_id"    INTEGER, -- 参数id
            "name"    TEXT NOT NULL, -- 参数名
            "key"    TEXT NOT NULL, -- 参数key
            "defual"    TEXT NOT NULL, -- 默认值
            "type"    TEXT NOT NULL, -- 类型
            "des"    TEXT NOT NULL, -- 参数描述
            PRIMARY KEY("id" AUTOINCREMENT)
        );
        """
    cur.execute(sql)
    # 策略
    sql = """
    CREATE TABLE IF NOT EXISTS "scheme" ( -- 策略表
        "id"    INTEGER NOT NULL UNIQUE,
        "template"    INTEGER, -- 参数id
        "name"    TEXT NOT NULL, -- 参数名
        PRIMARY KEY("id" AUTOINCREMENT)
    );
    """
    cur.execute(sql)
    # 策略参数
    sql = """
    CREATE TABLE IF NOT EXISTS "scheme_param" ( -- 方案参数表
        "id"    INTEGER NOT NULL UNIQUE,
        "scheme_id"    INTEGER NOT NULL, -- 方案id
        "key"   TEXT NOT NULL, -- 参数key 
        "value" TEXT NOT NULL, -- 参数值
        PRIMARY KEY("id" AUTOINCREMENT)
    );
    """
    cur.execute(sql)
    # 策略包含股票
    sql = """
    CREATE TABLE IF NOT EXISTS "scheme_codes" ( -- 方案包含的股票代码
        "id"    INTEGER NOT NULL UNIQUE,
        "scheme_id"    INTEGER NOT NULL, -- 方案id
        "type"    TEXT NOT NULL, -- 类型 0 股票 1 etf
        "code"    TEXT NOT NULL, -- 参数名
        PRIMARY KEY("id" AUTOINCREMENT)
    );
    """
    cur.execute(sql)
    # 回测
    sql = """
    CREATE TABLE IF NOT EXISTS "back_trade" ( -- 回测记录
        "id"    INTEGER NOT NULL UNIQUE,
        "scheme_id"    INTEGER NOT NULL, -- 方案id
        "date" TEXT NOT NULL, -- 时间
        "type"    TEXT NOT NULL, -- 类型 0 股票 1 etf
        "code"    TEXT NOT NULL, -- 代码code
        "price"    NUMERIC NOT NULL, -- 价格
        "dir"    INTEGER NOT NULL, -- 买卖方向 0 买入 1卖出
        "count"    INTEGER NOT NULL, -- 数量
        "sid"    INTEGER , -- 对冲ID 买入对应卖出订单 卖出对应买入订单
        PRIMARY KEY("id" AUTOINCREMENT)
    );
    """
    cur.execute(sql)

     # 回测
    # sql = """
    # CREATE TABLE IF NOT EXISTS "back_trade_surplus" ( -- 回测结余
    #     "id"    INTEGER NOT NULL UNIQUE,
    #     "scheme_id"    INTEGER NOT NULL, -- 方案id
    #     "date" TEXT NOT NULL, -- 时间
    #     "code"    TEXT NOT NULL, -- 代码code
    #     "surplus"    NUMERIC NOT NULL, -- 结余
    #     PRIMARY KEY("id" AUTOINCREMENT)
    # );
    # """
    # cur.execute(sql)
    con.commit()

def recreateTable():
    tables = [
        "scheme_template",
        "scheme_template_param",
        "scheme",
        "scheme_param",
        "scheme_codes",
        "back_trade"
    ]
    cur = con.cursor()
    for table in tables:
        sql = ("drop table IF EXISTS %s" % table)
        cur.execute(sql)
    con.commit()
    createTable()

createTable()